copyright (c) 1998 by Juergen Galupki

Nordstr. 30
D-53757 Sankt Augustin
GERMANY
 
E-mail: Juergen.Galupki@gmd.de
http://www.germany.net/teilnehmer/100/1894/Juergen.htm

Important note: Please forgive me my terrible english,							
i was born and live in germany (near Bonn) and so							
i speak and write much better in german language...							



The spreadsheet calculates the planned and actual	Changes please only at the faces set aside gray		
monthly working hours appropriate split			in the excel version !				
up to several projects.					Copy monthly and work with copy!		


Necessary:							

1. Work time begin and end, daily break							

   C1:C7 ... Work time begin monday - sunday							
             e.g.:   '08:00   eight'o clock in the morning							
   D1:D7 ... Work time end monday - sunday							
             e.g.:   '16:30   (in 24 hours format)							
   H2    ... Duration of break
             e.g.:   '00:30   for half an hour
   I2    ... Duration of working time after which a break MUST be made!
             e.g.:   '06:00   after 6 hours

   The information is used to determine the regular weekly working time.

   Is a daily working time greater than the value in I2,
   then the break-duration (H2) is automatically diverted.

   The computed working times per day are spent for control purposes.
   They are stored in F1:F7. F8 contains the weekly working time.

    1 hr.  = 1,0
   50 Min. = 0,833
   45 Min. = 0,75
   30 Min. = 0,5
   20 Min. = 0,333
   15 Min. = 0,25
   10 Min. = 0,167
    5 Min. = 0,083 etc.  (min/60 = hours)

   These information must be adjusted only once.

2. Actual month, year and holidays

   Type in actual month to C10, actual year to D10.
   
   I recommend every month to copy the file
   and or the worksheet and to work only with the copy !

   In line 11 to 41 the actual month will be prepared.
   Holidays in this month are automatically calculated
   if you have prepared this once.

   In the field "feiert" (C48:Q49) the holidays must be defined
   uniquely. All holidays which can fall at a working day must
   be defined. These holidays are then considered automatically.
   In line 48 the dates of the holidays has to be defined.
   Use the function
         =DATUM($B$9; month; day)    (DATE in english ?!)

   If the date of the holiday are relative to eastern, then
   you can use the cell B49 as reference (Easter Sunday).
   E.g. to define ascension day write down:
         =DATUM($B$49+39)   (DATE...)
   this means 39 days after Easter Sunday ! 
   More days dependent to Easter Sunday:

   -52 Weiberfastnacht		=DATUM($B$49-52) this are days
   -48 Rosenmontag		=DATUM($B$49-48) related on
   -47 Fastnacht		...		 the german
   -46 Aschermittwoch			         carnival ;-)
   - 2 Good Friday				
   + 0 Easter Sunday				
   + 1 Easter Monday				
   +39 Ascension Day				
   +49 Pentecost				
   +50 Pentecost Monday				
   +60 Corpus Christi				

   Please consider, that in this spreadsheet you can use				
   only up to max. 15 holidays !				

   In line 49 you can write down a short memo
   for the holiday. This memo is used in place of
   the daily working time on that days...

   Please consider to define the holidays in the right
   sequence in the year !

   The holidays in the sheets are the holidays
   for the german state Nordrhein-Westfalen with
   regional changes.

   Special holiday-definitions like the bank-holidays:
   this days are defined to be on a weekday (Monday...Sunday)
   at/after a fixed date.

      These types of holidays can defined in a formula too.
      Look at the example in the (hidden) cell R48:   (WENN=IF, RUNDEN=ROUND, DATUM=DATE...)	
	=WENN( 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) < 0; 
	DATUM($B$9;12;31)+ 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) +7; 
	DATUM($B$9;12;31)+ 1-REST(RUNDEN(DATUM($B$9;12;31);0);7) )
      This example calculates the date of the first sunday at/after the 31.12.	
      Copy this formula in a cell for the holidays and change it:	
      1. In DATUM($B$9;12;31) replace the 12 through the desired month	
         replace the 31 through the desired day (5 times) 	
      2. the 1 before -REST...) through the daynumber of the needed weekday. (3 times).	
         daynumber: 0=Sa, 1=Su, 2=Mo, 3=Tu, 4=We, 5=Th, 6=Fr	
      In PSION-Tab is REST to write as MOD...	

   Any ones holidays can calculated with my PSION OPL	
   freeware-Program KALENDER for S3c and Siena.	
   Look at my homepage -> Psion...	

   definitions of holidays for GB and US:

Independance Day          (US) 4.7. (fixed date)
Labor Day                 (US) Mo 1.9. (first monday at/after the 1.9.)
Martin Luther King Day    (US) Mo 15.1. (...)
Memorial Day              (US) Mo 25.5.
Presidents' Day           (US) Mo 17.2.
Thanksgiving Day          (US) Do 23.11.
Thanksgiving Friday       (US) Fr 24.11.
Veterans' Day             (US) 11.11.
Weihnachtstag             (US) 25.12.
Boxing Day                (GB) 26.12.
Christmas Day             (GB) 25.12.
Easter Monday Holiday     (GB) os+1 (means Easter sunday+1)
Easter Tuesday Holiday    (GB) os+2
May Day Holiday Monday    (GB) Mo 1.5.
New Year's Day            (GB) 1.1.
Spring Bank Holiday       (GB) Mo 25.5.
Summer Bank Holiday       (GB) Mo 25.8.
August Bank Holiday     (GB-I) Mo 1.8.
June Bank Holiday       (GB-I) Mo 1.6.
October Bank Holiday    (GB-I) Mo 25.10.
St.Patrick's Day        (GB-I) 17.3.
St.Stephen's Day        (GB-I) 26.12.
12th of July            (GB-N) 12.7.
January Bank Holiday    (GB-S) 2.1.
Spring Bank Holiday     (GB-S) Mo 1.5.
Summer Bank Holiday     (GB-S) Mo 1.8.


3. Projects

   If needed, you can divide your daily working time
   in more than one projects. The denotations has to
   fill in H9:P9. The first (mean) Project becomes
   the time which is calculated: daily working time
   minus the other 9 projects.
   If the number in H is negative, than you have
   more time distributed to the 9 projects than
   you haved worked at this day !

   The monthly sum for every project is shown. Additional
   a graphik can be called (Diamond Button at the Psion).
   The graphic shows the projects with there parts...

   The input here has to be in hours, e.g. an hour and 
   30 minutes has to be written as 1,5 !


Use:

Adapt the sheet to your special situation.

At the start of a new month copy the sheet. Use
the copy for your work. After the month you can archive 
this copy when needed...

Write the number of the new month to cell C10 and
if needed (december->january) the year (2 digits, year 2000 = 100)
to D10. The new month is now calculated...

In the lines 11-41 you can see the following data:

row A: weekday (Mo, Tu, ...Su)
row B: date (day.month.)

row C: begin of working time. A suggestion is
automatically placed here (if you have defined your
weekly scheduling). If needed you can overwrite
the planned time. Type in a ' and the time in HH:MM,
e.g. '07:50 or '08:55... In this case row D
is actualized...

row D: end of daily working time (same as row C
for begin...). Overwrite if necessary...
The break is considered automatically.

The Psion calculates in row E the actual daily
workingtime (without break). If a holiday has
been identified, here stands the memo of the
day. If you have worked, you have to put the
time manually in!

In row F and G the difference to a "normal" day
is written out (if you have changed the rows C or D!).
row F: time in hours
row G: time in hours:minutes

In the rows I to Q you can enter the time
for the special projects (only in hours,
e.g. 1,5 for 1 hour 30 minutes). If the sum
of the individual projects is smaller than
the day working time, the rest is autom.
written to the mean project in row H, also
the following formula is used:
H = E - SUMME(I:Q)
H   = mean project
E   = day working time
I:Q = more projects...

If you don't have more than one project, the
mean projekt (row H) also includes the complete
day working time (row E).

In row R you can enter a remark like e.g. "sick",
"vacation" and enter this time under a project
like "Absent"...


SUMMARY

In line 43 the monthly sum is calculated:
row E: actual working time
row F: difference to the planned working time
rows H-Q: sum of each project

cell E44: Actual month hours

cell F45: here you can enter the credit/debit time
of the last month in hours !

cell F46: here is calculated the credit/debit time
for the next month: Planned working time minus
actual working time plus credit/debit last month.

The cells F45 and F46 are (calculated) in hours.
In H45 and H46 they were translated to hours:minutes
so it is easier to read.

Questions, mistakes, whishes (money ;-)) and so on via Email to me:

Juergen.Galupki@gmd.de

Please take a look at my homepage at
http://www.germany.net/teilnehmer/100.1894/Juergen.htm
Psion-related freeware at
http://www.germany.net/teilnehmer/100.1894/psion.htm


